﻿
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data;
using FastReflectionLib;
using JYH.Data;
using JYH.Framework;


namespace JYH.DataAccess
{
    public class DA<T> where T : class
    {
       private static Database _db = null;
        public static Database myDatabase
        {
            get
            {
                return _db;
            }
            set
            {
                _db = value;
            }
        }

        private static Database GetDB()
        {

            Database db;

           
            if (ConfigHelper.CheckConnectionStringKey("DBCONN"))
            {
                db = DB.D("DBCONN");
            }

            else
            {
                db = myDatabase;
            }
            return db;
        }

        /// <summary>
        /// 根据主键ID获取对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static T FetchSingleById(string id)
        {
            Database db= GetDB();
            
            var pd = JYHData.ForType(typeof(T));
            return db.SingleOrDefault<T>("WHERE " + pd.TableInfo.PrimaryKey + "=@0", id);
        }

        /// <summary>
        /// 根据主键ID查询信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static T Single(object id)
        {
            Database db= GetDB();
            return db.Single<T>(id);
        }

        /// <summary>
        /// 查询单个对象
        /// </summary>
        /// <param name="sqlWhere"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static T SingleOrDefault(Sql sql)
        {
            Database db= GetDB();           
            return db.SingleOrDefault<T>(sql);
        }

        /// <summary>
        /// 查询单个对象
        /// </summary>
        /// <param name="sqlWhere"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static T SingleOrDefault(string sqlWhere, params object[] args)
        {
            Database db= GetDB();
            return db.SingleOrDefault<T>(sqlWhere, args);
        }

        /// <summary>
        /// 获取所有数据
        /// </summary>
        /// <returns></returns>
        public static List<T> FetchAll()
        {
            Database db= GetDB();
            
            return db.Fetch<T>("", null);
        }

        /// <summary>
        /// 根据Sql语句查询数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<T> Fetch(Sql sql)
        {
            Database db= GetDB();
            return db.Fetch<T>(sql);
        }

        /// <summary>
        /// 根据sql语句查询数据
        /// 支持In查询
        /// 例：db.Fetch("Where field in (@val)",new {@val=value})
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<T> Fetch(string sqlWhere, params object[] args)
        {
            Database db= GetDB();
           
            return db.Fetch<T>(sqlWhere, args);
        }

        /// <summary>
        /// 获取单个对象信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static T FetchSingle(Sql sql)
        {
            List<T> list = Fetch(sql);
            if (list != null && list.Count > 0)
                return list[0];

            return null;
        }

        /// <summary>
        /// 获取单个对象信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static T FetchSingle(string sqlWhere, params object[] args)
        {
            Database db= GetDB();
             
            List<T> list = db.Fetch<T>(sqlWhere, args);
            if (list != null && list.Count > 0)
                return list[0];

            return null;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sql"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<T> PagedQuery(long pageIndex, long pageSize, Sql sql, out long total)
        {
            Database db= GetDB();
            
            var result = db.Page<T>(pageIndex, pageSize, sql);
            total = result.TotalItems;
            return result.Items;
        }
        public static List<T> PagedQuery( Sql sql)
        {
            Database db= GetDB();
            var result = db.Fetch<T>(sql);
            return result;
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="total"></param>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static List<T> PagedQuery(long pageIndex, long pageSize, out long total, string sql, params object[] args)
        {
            Database db= GetDB();
             var result = db.Page<T>(pageIndex, pageSize, sql, args);
            total = result.TotalItems;
            return result.Items;
        }
        public static List<T> PagedQuery( string sql, params object[] args)
        {
            Database db= GetDB();
         
            var result = db.Fetch<T>(sql, args);
            return result;
        }
        /// <summary>
        /// 保存(新增/修改)
        /// </summary>
        /// <param name="t"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool Save(T t, string id)
        {
            Database db= GetDB();
          
            var pd = JYHData.ForType(t.GetType());
            using (db)
            {
                try
                {
                    var tTableName = t.GetType().Name;
                    var actionOn = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                    db.BeginTransaction();

                    var tObj = db.SingleOrDefault<T>("Where " + pd.TableInfo.PrimaryKey + "=@0", id);
                    if (tObj == null)
                    {
                        //新增主档
                        if (!IsTrue(db.Insert(t)))
                        {
                            db.AbortTransaction();
                            return false;
                        }
                    }
                    else
                    {
                        //更新主档
                        if (db.Update(t) <= 0)
                        {
                            db.AbortTransaction();
                            return false;
                        }
                    }
                    db.CompleteTransaction();

                    return true;
                }
                catch (Exception ex)
                {
                    db.AbortTransaction();
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 批量保存
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static bool Save(List<T> list)
        {
            Database db= GetDB();
           var pd = JYHData.ForType(typeof(T));
            using (db)
            {
                try
                {
                    var tTableName = typeof(T).Name;
                    var actionOn = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                    db.BeginTransaction();

                    foreach (var t in list)
                    {
                        var idValue = t.GetType().GetProperty(pd.TableInfo.PrimaryKey).FastGetValue(t).ToString();

                        var tObj = db.SingleOrDefault<T>("Where " + pd.TableInfo.PrimaryKey + "=@0", idValue);
                        if (tObj == null)
                        {
                            //新增主档
                            if (!IsTrue(db.Insert(t)))
                            {
                                db.AbortTransaction();
                                return false;
                            }
                        }
                        else
                        {
                            //更新主档
                            if (db.Update(t) <= 0)
                            {
                                db.AbortTransaction();
                                return false;
                            }
                        }
                    }

                    db.CompleteTransaction();

                    return true;
                }
                catch (Exception ex)
                {
                    db.AbortTransaction();
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 批量保存
        /// </summary>
        /// <param name="newOrUpdatelist"></param>
        /// <param name="deleteList"></param>
        /// <returns></returns>
        public static bool Save(List<T> newOrUpdatelist, List<T> deleteList)
        {
            Database db= GetDB();
          
            var pd = JYHData.ForType(typeof(T));
        

            using (db)
            {
                try
                {
                    var tTableName = typeof(T).Name;
                    var actionOn = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                    db.BeginTransaction();

                    foreach (var t in newOrUpdatelist)
                    {
                        var idValue = t.GetType().GetProperty(pd.TableInfo.PrimaryKey).FastGetValue(t).ToString();

                        var tObj = db.SingleOrDefault<T>("Where " + pd.TableInfo.PrimaryKey + "=@0", idValue);
                        if (tObj == null)
                        {
                            //新增主档
                            if (!IsTrue(db.Insert(t)))
                            {
                                db.AbortTransaction();
                                return false;
                            }
                        }
                        else
                        {
                            //更新主档
                            if (db.Update(t) <= 0)
                            {
                                db.AbortTransaction();
                                return false;
                            }
                        }
                    }
                    foreach (var t in deleteList)
                    {
                        if (db.Delete(t) <= 0)
                        {
                            db.AbortTransaction();
                            return false;
                        }
                    }

                    db.CompleteTransaction();

                    return true;
                }
                catch (Exception ex)
                {
                    db.AbortTransaction();
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 针对含有主档和明细的项目保存（新增或修改）
        /// </summary>
        /// <typeparam name="D">明细类型</typeparam>
        /// <param name="t">主档对象</param>
        /// <param name="tid">主档主键值</param>
        /// <param name="details">明细列表</param>
        /// <returns></returns>
        public static bool Save<D>(T t, string tId, List<D> details) where D : class
        {

            Database db= GetDB();
            var pd = JYHData.ForType(t.GetType());
            using (db)
            {
                try
                {
                    var tTableName = t.GetType().Name;
                    var dTableName = typeof(D).Name;
                    var actionOn = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                    db.BeginTransaction();

                    var tObj = db.SingleOrDefault<T>("Where " + pd.TableInfo.PrimaryKey + "=@0", tId);
                    if (tObj == null)
                    {
                        //新增主档
                        if (!IsTrue(db.Insert(t)))
                        {
                            db.AbortTransaction();
                            return false;
                        }

                        if (details != null && details.Count > 0)
                        {
                            //新增明细
                            foreach (var detail in details)
                            {
                                if (!IsTrue(db.Insert(detail)))
                                {
                                    db.AbortTransaction();
                                    return false;
                                }
                            }
                        }
                    }
                    else
                    {
                        //更新主档
                        if (db.Update(t) <= 0)
                        {
                            db.AbortTransaction();
                            return false;
                        }

                        if (details != null && details.Count > 0)
                        {
                            //约定规则，外键名称用主表的表名+Id
                            var parentIdName = pd.TableInfo.PrimaryKey;

                            //数据库中已有的明细项目
                            var oldDetails = db.Fetch<D>(string.Format("WHERE {0}='{1}'", parentIdName, tId));

                            if (oldDetails == null || oldDetails.Count <= 0)
                            {//原来无明细，则全部新增
                                foreach (var detail in details)
                                {
                                    if (!IsTrue(db.Insert(detail)))
                                    {
                                        db.AbortTransaction();
                                        return false;
                                    }
                                }
                            }
                            else
                            {
                                #region* 找出所有原有明细的Id *
                                List<string> oldIds = new List<string>();
                                foreach (var old in oldDetails)
                                {
                                    var pdDetail = JYHData.ForType(old.GetType());
                                    PropertyInfo p = old.GetType().GetProperty(pdDetail.TableInfo.PrimaryKey);
                                    var oldId = p.FastGetValue(old).ToString();
                                    if (!oldIds.Contains(oldId))
                                        oldIds.Add(oldId);
                                }
                                #endregion

                                #region* 找出所有传入明细的Id *
                                List<string> ids = new List<string>();
                                foreach (var detail in details)
                                {
                                    var pdDetail = JYHData.ForType(detail.GetType());
                                    PropertyInfo p = detail.GetType().GetProperty((pdDetail.TableInfo.PrimaryKey));
                                    var id = p.FastGetValue(detail).ToString();
                                    if (!ids.Contains(id))
                                        ids.Add(id);
                                }
                                #endregion

                                //新增的明细项目Id
                                var newIds = new List<string>();
                                //修改的明细项目Id
                                var updateIds = new List<string>();
                                //删除的明细项目Id
                                var deleteIds = new List<string>();


                                #region *传入的明细列表 找出要新增和要修改的明细Id*
                                foreach (var id in ids)
                                {
                                    if (oldIds.Contains(id))
                                        updateIds.Add(id);
                                    else
                                        newIds.Add(id);
                                }
                                #endregion

                                #region* 原有的明细列表中找出要删除的明细Id *
                                foreach (var oId in oldIds)
                                {
                                    if (!ids.Contains(oId))
                                        deleteIds.Add(oId);
                                }
                                #endregion

                                //新增的明细项目Id
                                var newDetails = new List<D>();
                                //修改的明细项目Id
                                var updateDetails = new List<D>();
                                //删除的明细项目Id
                                var deleteDetails = new List<D>();

                                #region* 从数据库原有明细中找出删除的明细 *
                                foreach (var old in oldDetails)
                                {
                                    var pdDetail = JYHData.ForType(old.GetType());

                                    PropertyInfo p = old.GetType().GetProperty(pdDetail.TableInfo.PrimaryKey);
                                    var oldId = p.FastGetValue(old).ToString();

                                    if (deleteIds.Contains(oldId))
                                        deleteDetails.Add(old);
                                }
                                #endregion

                                #region* 从传入的明细中找出新增和修改的明细 *
                                foreach (var detail in details)
                                {
                                    var pdDetail = JYHData.ForType(detail.GetType());
                                    PropertyInfo p = detail.GetType().GetProperty((pdDetail.TableInfo.PrimaryKey));
                                    var id = p.FastGetValue(detail).ToString();

                                    if (newIds.Contains(id))
                                        newDetails.Add(detail);
                                    else
                                        updateDetails.Add(detail);
                                }
                                #endregion

                                #region* 保存删除明细 *
                                foreach (var detail in deleteDetails)
                                {
                                    if (db.Delete(detail) <= 0)
                                    {
                                        db.AbortTransaction();
                                        return false;
                                    }
                                }
                                #endregion

                                #region* 保存修改明细 *
                                foreach (var detail in updateDetails)
                                {
                                    if (db.Update(detail) <= 0)
                                    {
                                        db.AbortTransaction();
                                        return false;
                                    }
                                }
                                #endregion

                                #region* 保存新增明细 *
                                foreach (var detail in newDetails)
                                {
                                    if (!IsTrue(db.Insert(detail)))
                                    {
                                        db.AbortTransaction();
                                        return false;
                                    }
                                }
                                #endregion
                            }
                        }
                    }

                    db.CompleteTransaction();

                    return true;
                }
                catch (Exception ex)
                {
                    db.AbortTransaction();
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <typeparam name="D"></typeparam>
        /// <param name="t"></param>
        /// <param name="tId"></param>
        /// <param name="d"></param>
        /// <param name="dId"></param>
        /// <returns></returns>
        public static bool Save<D>(T t, string tId, D d, string dId) where D : class
        {
            Database db= GetDB();
            using (db)
            {
                try
                {
                    var tTableName = t.GetType().Name;
                    var actionOn = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    var pd = JYHData.ForType(t.GetType());
                    db.BeginTransaction();

                    var tObj = db.SingleOrDefault<T>("Where " + pd.TableInfo.PrimaryKey + "=@0", tId);
                    if (tObj == null)
                    {
                        //新增主档
                        if (!IsTrue(db.Insert(t)))
                        {
                            db.AbortTransaction();
                            return false;
                        }
                    }
                    else
                    {
                        //更新主档
                        if (db.Update(t) <= 0)
                        {
                            db.AbortTransaction();
                            return false;
                        }
                    }

                    var pds = JYHData.ForType(d.GetType());
                    var dObj = db.SingleOrDefault<D>("Where " + pds.TableInfo.PrimaryKey + "=@0", dId);
                    if (dObj == null)
                    {
                        //新增主档
                        if (!IsTrue(db.Insert(d)))
                        {
                            db.AbortTransaction();
                            return false;
                        }
                    }
                    else
                    {
                        //更新主档
                        if (db.Update(d) <= 0)
                        {
                            db.AbortTransaction();
                            return false;
                        }
                    }
                    db.CompleteTransaction();

                    return true;
                }
                catch (Exception ex)
                {
                    db.AbortTransaction();
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 插入一条数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public static bool Insert(T t)
        {
            Database db= GetDB();
            var result = db.Insert(t);
            return IsTrue(result);
        }
        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static bool Insert(List<T> list)
        {
            Database db= GetDB();
          
            using (db)
            {
                var actionOn = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                db.BeginTransaction();

                foreach (var t in list)
                {
                    //新增主档
                    if (!IsTrue(db.Insert(t)))
                    {
                        db.AbortTransaction();
                        return false;
                    }
                }
                db.CompleteTransaction();

                return true;
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public static bool Update(T t)
        {
            Database db= GetDB();
           
            return db.Update(t) > 0;
        }

        /// <summary>
        /// 执行sql更新数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static bool Update(string sql, params object[] args)
        {
            Database db= GetDB();
            return db.Update<T>(sql, args) > 0;
        }

        /// <summary>
        /// 删除 
        /// </summary>
        /// <param name="t">类名</param>
        /// <returns></returns>
        public static bool Delete(List<T> listT)
        {
            Database db= GetDB();
            using (db)
            {
                try
                {
                    var tTableName = typeof(T).Name;
                    var actionOn = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                    db.BeginTransaction();

                    for (int i = 0; i < listT.Count; i++)
                    {
                        //删除主档
                        if (db.Delete(listT[i]) <= 0)
                        {
                            db.AbortTransaction();
                            return false;
                        }
                    }
                    db.CompleteTransaction();

                    return true;
                }
                catch (Exception ex)
                {
                    db.AbortTransaction();
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 删除（主档和明细）
        /// </summary>
        /// <typeparam name="D">明细类型</typeparam>
        /// <param name="t">主档对象</param>
        /// <param name="details">明细列表</param>
        /// <returns></returns>
        public static bool Delete<D>(T t, List<D> details) where D : class
        {
            Database db= GetDB();
           
            using (db)
            {
                try
                {
                    var tTableName = t.GetType().Name;
                    var actionOn = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

                    db.BeginTransaction();

                    //查询所有原始明细

                    //约定规则，外键名称用主表的表名+Id
                    var pd = JYHData.ForType(t.GetType());
                    var parentIdName = pd.TableInfo.PrimaryKey;
                    var pds = JYHData.ForType(typeof(D));
                    var dTableName = typeof(D).Name;
                    var tId = t.GetType().GetProperty(pd.TableInfo.PrimaryKey).FastGetValue(t).ToString();

                    //数据库中已有的明细项目
                    var oldDetails = db.Fetch<D>(string.Format("WHERE {0}='{1}'", parentIdName, tId));

                    foreach (var detail in oldDetails)
                    {
                        if (db.Delete(detail) <= 0)
                        {
                            db.AbortTransaction();
                            return false;
                        }
                    }
                    //删除主档
                    if (db.Delete(t) <= 0)
                    {
                        db.AbortTransaction();
                        return false;
                    }
                    db.CompleteTransaction();

                    return true;
                }
                catch (Exception ex)
                {
                    db.AbortTransaction();
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 删除 (根据条件删)
        /// </summary>
        /// <param name="sqlWhere"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static bool Delete(string sqlWhere, params object[] args)
        {
            Database db= GetDB();
          
            return db.Delete<T>(sqlWhere, args) > 0;
        }

        /// <summary>
        /// 执行sql语句(非查询)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static int Execute(string sql, params object[] args)
        {
            Database db= GetDB();
           return db.Execute(sql, args);
        }

        /// <summary>
        /// 执行sql语句(非查询)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static int Execute(Sql sql)
        {
            Database db= GetDB();
            return db.Execute(sql);
        }

        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <param name="t"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        private static bool IsNew(T t, string id)
        {
            Database db= GetDB();
           
            var pd = JYHData.ForType(t.GetType());
            return db.SingleOrDefault<T>("WHERE " + pd.TableInfo.PrimaryKey + "=@0", id) == null;
        }

        /// <summary>
        /// 判断是否 true 
        /// </summary>
        /// <param name="result"></param>
        /// <returns></returns>
        private static bool IsTrue(object result)
        {
            return !(string.IsNullOrEmpty(result.ToString()));
        }


    }
    
}
